﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using FX.CC.Andon.Entity.BasicDataManage;
using FX.CC.Andon.Entity.SystemManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.SystemManage
{
    public class OpLinesService : DbHelperBase
    {
        public int GetLineListByIdCount(OpLinesQcEntity qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [count] FROM UT_OpLines AS A  ");
            strSql.Append(" INNER JOIN UT_Line AS B ON A.LineID=B.LineID ");
            strSql.Append(" WHERE A.UserID=@UserID ");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserID", qcmodel.UserID);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<OpLinesEntity> GetLineListById(OpLinesQcEntity qcmodel)
        {
            var list = new List<OpLinesEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            int start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            int end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by InputTime) AS idx,A.RowID,B.LineID,B.LineCode,B.LineName FROM UT_OpLines AS A ");
            strSql.Append(" INNER JOIN UT_Line AS B ON A.LineID=B.LineID ");
            strSql.Append(" WHERE A.UserID=@UserID ");
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserID", qcmodel.UserID);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new OpLinesEntity();
                            if (reader["RowID"] != DBNull.Value)
                                model.RowID = new Guid(reader["RowID"].ToString());
                            if (reader["LineID"] != DBNull.Value)
                                model.LineID = new Guid(reader["LineID"].ToString());
                            model.LineCode = reader["LineCode"] == obj ? null : Convert.ToString(reader["LineCode"]);
                            model.LineName = reader["LineName"] == obj ? null : Convert.ToString(reader["LineName"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public int GetOpLinesCount(OpLinesQcEntity qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [count] FROM (  ");
            strSql.Append(" SELECT distinct A.UserID  FROM dbo.UT_OpLines AS A  ");
            strSql.Append(" INNER JOIN dbo.UT_User AS B ON A.UserID=B.UserID  WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.LoginCode))
            {
                strSql.Append(" AND B.LoginCode LIKE '%" + qcmodel.LoginCode + "%'");
            }
            if (!string.IsNullOrEmpty(qcmodel.FullName))
            {
                strSql.Append(" AND B.FullName LIKE '%" + qcmodel.FullName + "%'");
            }
            strSql.Append("  ) AS T ");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public IList<OpLinesEntity> GetOpLinesList(OpLinesQcEntity qcmodel)
        {
            var list = new List<OpLinesEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            int start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            int end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT X.* FROM( ");
            strSql.Append(" SELECT  row_number() over (order by T.LoginCode) AS idx,T.* FROM ( ");
            strSql.Append(" SELECT distinct A.UserID,B.LoginCode,B.FullName  FROM dbo.UT_OpLines AS A  ");
            strSql.Append(" INNER JOIN dbo.UT_User AS B ON A.UserID=B.UserID WHERE 1=1 ");
            strSql.Append(" )AS T WHERE 1=1  ");
            if (!string.IsNullOrEmpty(qcmodel.LoginCode))
            {
                strSql.Append(" AND T.LoginCode LIKE '%" + qcmodel.LoginCode + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.FullName))
            {
                strSql.Append(" AND T.FullName LIKE '%" + qcmodel.FullName + "%' ");
            }
            strSql.Append(" ) AS X ");

            strSql.Append(" Where X.idx between " + start + " and " + end);

            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }

            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new OpLinesEntity();
                            if (reader["UserID"] != DBNull.Value)
                                model.UserID = new Guid(reader["UserID"].ToString());
                            model.LoginCode = reader["LoginCode"] == obj ? null : Convert.ToString(reader["LoginCode"]);
                            model.FullName = reader["FullName"] == obj ? null : Convert.ToString(reader["FullName"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool AddOpLines(List<OpLinesEntity> list)
        {
            var result = false;
            var conn = Db.GetConnection() as SqlConnection;
            if (conn == null)
            {
                throw new InvalidOperationException("No Valid Database Connection!");
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            using (var bcp = new SqlBulkCopy(conn))
            {
                var mapRowId =
                    new SqlBulkCopyColumnMapping("RowID", "RowID");
                bcp.ColumnMappings.Add(mapRowId);

                var mapUserId =
                    new SqlBulkCopyColumnMapping("UserID", "UserID");
                bcp.ColumnMappings.Add(mapUserId);

                var mapLineId =
                    new SqlBulkCopyColumnMapping("LineID", "LineID");
                bcp.ColumnMappings.Add(mapLineId);

                bcp.DestinationTableName = "UT_OpLines";
                try
                {
                    bcp.WriteToServer(Collection.ToDataTable(list));
                    result = true;
                    conn.Close();
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveOpLinesByUserID(string id)
        {
            var strSql = new StringBuilder();
            strSql.Append(" Delete From UT_OpLines Where UserID=@UserID ");
            var result = false;
            using (DbCommand cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@UserID", id);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool RemoveOpLinesByRowID(string id)
        {
            var strSql = new StringBuilder();
            strSql.Append(" Delete From UT_OpLines Where RowID=@RowID ");
            var result = false;
            using (DbCommand cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@RowID", id);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
    }
}
